package cn.jeefast.util;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class ExcelUtil {
    int CELL_TYPE_NUMERIC = 0;
    int CELL_TYPE_STRING = 1;
    int CELL_TYPE_FORMULA = 2;
    int CELL_TYPE_BLANK = 3;
    int CELL_TYPE_BOOLEAN = 4;
    int CELL_TYPE_ERROR = 5;


    public static String impPriceRecord() throws Exception {
        // String path = "E:\\dataList.xlsx";
        String path1 = "C:\\Users\\Wade\\Desktop\\job\\.xlsx";
        try (InputStream is = new FileInputStream(path1)) {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(2);
            for (int i = 1; i <= xssfSheet.getLastRowNum(); i++) {
                XSSFRow xssfRow = xssfSheet.getRow(i);
                System.out.println(xssfRow.toString());
            }
        }
        return path1;
    }

    public static List<List<String>> readExcelWithSheet(File file, Integer sheetNum) throws IOException {
        List<String> stringList = Lists.newArrayList();
        List<List<String>> returnList = Lists.newArrayList();
        InputStream is = null;
        Workbook wb = null;
        boolean isE2007 = false;
        if (file.getPath().toString().endsWith("xlsx")) {
            isE2007 = true;
        }
        is = new FileInputStream(file);
        if (isE2007) {
            wb = new XSSFWorkbook(is);
        } else {
            wb = new HSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(sheetNum);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            stringList = Lists.newArrayList();
            Row row = rows.next();
            Iterator<Cell> cells = row.cellIterator();
            while (cells.hasNext()) {
                Cell cell = cells.next();
                switch (cell.getCellType()) {
//                    case HSSFCell.CELL_TYPE_NUMERIC:
//                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
////                        System.out.println("日期类型");
////                            stringList.add(DateUtil.getAllStrDate(cell.getDateCellValue()));
//                        } else {
////                        System.out.println("数字类型");
//                            stringList.add(String.valueOf(cell.getNumericCellValue()));
//                        }
//                        break;
//                    case HSSFCell.CELL_TYPE_STRING:
////                        System.out.println("String类型");
//                        stringList.add(String.valueOf(cell.getStringCellValue()));
//                        break;
//                    case HSSFCell.CELL_TYPE_FORMULA:
////                        System.out.println("公式类型");
//                        stringList.add(String.valueOf(cell.getCellFormula()));
//                        break;
//                    case HSSFCell.CELL_TYPE_BLANK:
////                        System.out.println("空值");
//                        stringList.add(new String());
//                        break;
//                    case HSSFCell.CELL_TYPE_BOOLEAN:
////                        System.out.println("Boolean类型");
//                        stringList.add(String.valueOf(cell.getBooleanCellValue()));
//                        break;
//                    case HSSFCell.CELL_TYPE_ERROR:
////                        System.out.println("错误类型");
//                        stringList.add(String.valueOf(cell.getErrorCellValue()));
//                        break;
                    default:
                        stringList.add(cell.toString());
                        break;
                }
            }
            returnList.add(stringList);
        }
        wb.close();
        is.close();
        return returnList;
    }

    //清欠
//    public static List<List<String>> readExcelWithSheet(File file, Integer sheetNum) throws IOException {
//        List<String> stringList = Lists.newArrayList();
//        List<List<String>> returnList = Lists.newArrayList();
//        InputStream is = null;
//        Workbook wb = null;
//        boolean isE2007 = false;
//        if (file.getPath().toString().endsWith("xlsx")) {
//            isE2007 = true;
//        }
//        is = new FileInputStream(file);
//        if (isE2007) {
//            wb = new XSSFWorkbook(is);
//        } else {
//            wb = new HSSFWorkbook(is);
//        }
//        Sheet sheet = wb.getSheetAt(sheetNum);
//        Iterator<Row> rows = sheet.rowIterator();
//        while (rows.hasNext()) {
//            stringList = Lists.newArrayList();
//            Row row = rows.next();
//            Iterator<Cell> cells = row.cellIterator();
//            while (cells.hasNext()) {
//                Cell cell = cells.next();
//                switch (cell.getCellType()) {
//                    case NUMERIC:
//                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
//                            //System.out.println("日期类型");
//                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//                            String date = sdf.format(cell.getDateCellValue());
//
//                            // Date date=cell.getDateCellValue();
//                            stringList.add(date);
//                            //     System.out.println(date);
//                        } else {
//                            //   System.out.println("数字类型");
//                            stringList.add(String.valueOf(cell.getNumericCellValue()));
//                        }
//                        break;
//                    case STRING:
//                        // System.out.println("String类型");
//                        stringList.add(String.valueOf(cell.getStringCellValue()));
//                        break;
////                    case HSSFCell.CELL_TYPE_FORMULA:
////                        System.out.println("公式类型");
////                        stringList.add(String.valueOf(cell.getCellFormula()));
////                        break;
//                    case BLANK:
//                        //   System.out.println("空值");
//                        stringList.add("");
//                        break;
////                    case HSSFCell.CELL_TYPE_BOOLEAN:
////                        System.out.println("Boolean类型");
////                        stringList.add(String.valueOf(cell.getBooleanCellValue()));
////                        break;
////                    case HSSFCell.CELL_TYPE_ERROR:
////                        System.out.println("错误类型");
////                        stringList.add(String.valueOf(cell.getErrorCellValue()));
////                        break;
////                    default:
////                        stringList.add(cell.toString());
////                        break;
//                }
//            }
//            returnList.add(stringList);
//        }
//        wb.close();
//        is.close();
//        return returnList;
//    }


    public static void main(String[] args) {
        String path = "C:\\Users\\Wade\\Desktop\\job\\数据0128.xlsx";

        try {
            List<List<String>> returnList = readExcelWithSheet(new File(path), 0);
            System.out.println(JSON.toJSONString(returnList));
        } catch (IOException e) {
            System.out.println(e);
        }

    }
}
